<?php
//	session_start();
	
	include "../../../config/php/config.php";
	include_once "../classes/MysqlConnection.class.php";
		
	class MySQLService
	{
		var $connection;
		
	    function MySQLService()
	    {
	        $this->methodTable = array
	        (
				"execute" => array
	            (
	                "access" => "remote",
	                "description" => "Executes a query."
	            ),
				"getCharsets" => array
	            (
	                "access" => "remote",
	                "description" => "Gets all the supported character sets by the server."
	            ),
				"getCollations" => array
	            (
	                "access" => "remote",
	                "description" => "Lists the collations supported by the server for one character set or all of them."
	            ),
	            "getDbList" => array
	            (
	                "access" => "remote",
	                "description" => "Gives back the name of the default database."
	            ),
				"dbLogin" => array
	            (
	                "access" => "remote",
	                "description" => "Makes authenticatoin for the database user."
	            )
	        );
			
			$mysqlConnection = new MysqlConnection( HOST_NAME, DB_USER_NAME, DB_PASSWORD, DB_NAME );
			$this->connection = $mysqlConnection->getConnection();
	    }
		
		
		/**
		 * Executes a query on the database.
		 * @return Response from database
		 */
		function execute( $query )
		{				
			if( isset( $query ) )
			{
				$result = mysql_query( $query, $this->connection );
				
				if( $result )
				{									
					$rows = array();
					
					$fields_num = 0;
					$row_num = 0;
					$aff_rows = 0;
					
					if( is_bool( $result ) )
					{
						//checking if there were any rows modified
						$aff_rows = mysql_affected_rows( $this->connection );
						
						return array( 	"status" => "OK", 									//status of query
										"details" => "DML_QUERY_RESULT", 					//type of result
										"result" => $aff_rows, 								//the rows affected by the query
										"information" => mysql_info( $this->connection ) );	//detailed information about the query
					}
					else
					{
						//getting the number of fields and rows
						$fields_num = mysql_num_fields( $result );
						$row_num = mysql_num_rows( $result );
						
						//fetching data
						while ( $row = mysql_fetch_assoc( $result ) ) 
						{
							$rows []= $row;
						}
						
						if( $row_num == 0 )
						{
							return array( 	"status" => "OK", 					//status of query
											"details" => "NO_RESULT_FOUND", 	//type of result: nothing was found and returned by the query
											"result" => null );					//nothing returned
						}
						else if( $fields_num == 1 && $row_num == 1 )
						{
							return array( 	"status" => "OK", 					//status of query
											"details" => "IS_SCALAR_RESULT", 	//type of result: only one record and one field was selected
											"result" => current($rows[0]) );	//resultset
						}
						else
						{
							return array( 	"status" => "OK", 					//status of query
											"details" => "IS_VECTOR_RESULT",	//type of result: one or more than one records were found 
											"result" => $rows, 					//resultset
											"num_rows" => $row_num, 			//number of rows in resultset
											"num_fields" => $fields_num ); 		//number of fields in resultset
						}
					}					
				}
				else
				{
					return array( "status" => "ERROR", "errorType" => "QUERY", "details" => mysql_error() );
				}
			}
			else
			{
				return array( "status" => "ERROR", "errorType" => "QUERY_EMPTY", "details" => "QUERY_EMPTY" );
			}
		}
		
		
		/**
		 * Gets all the supported character sets by the server.
		 * @return array of character sets
		 */
		function getCharsets()
		{
			//query for the charsets
			$sql = "show character set";
			
			$result = mysql_query( $sql, $this->connection );
			
			$character_sets = array();
			$character_sets []= '[default]';
			
			//fetching data
			while( $row = mysql_fetch_assoc( $result ) )
			{
				$character_sets []= $row['Charset'];
			}
			
			return array( "status" => "OK", "details" => "IS_VECTOR_RESULT", "result" => $character_sets );
		}
		
		
		/**
		 * Lists the collations supported by the server for
		 * one character set or all of them.
		 * 
		 * @return array of collations
		 * @param string $charset
		 */
		function getCollations( $charset )
		{
			//variable init
			$sql = '';
			$collations = array();
			$collations []= '[default]';
			
			//---
			// check if the $charset is [default].. In that case we need to show
			// all collations, else we must query for one charset.
			//---			
			if( $charset == '[default]' )
			{
				//query for all collations
				$sql = "show collation";
			}
			else
			{
				//query for collation by a charset
				$sql = "show collation where charset = '".$charset."'";
			}
			
			//common part
			$result = mysql_query( $sql );
			
			//fetching data
			while ( $row = mysql_fetch_assoc( $result ) ) 
			{
				$collations []= $row['Collation'];
			}
			
			return array( "status" => "OK", "details" => "IS_VECTOR_RESULT", "result" => $collations );
		}
	 
	 
	 	/**
	 	 * Builds a tree from database, getting databases, tables, triggers etc.
	 	 * @return Array (Data of the db in tree format.)
	 	 */
		function getDbList()
		{
			//query mysql, to get the list of the databases
			$result = mysql_list_dbs( $this->connection );
			
			$databases = array();
			
			//fetching the list of databases
			while( $row = mysql_fetch_assoc( $result ) )
			{
				$databases []= $row;
			}
									
			
			foreach( $databases as $key => $value )
			{
				/**
				 * ---------------------------------------------
				 * BUILDING INITIAL STRUCTURE OF A DB INSTANCE.
				 * ---------------------------------------------
				 */
				$databases[ $key ] = array( 
											"label" => $databases[ $key ][ "Database" ],
											"data" => $databases[ $key ][ "Database" ],
											"icon" => "mxDbIcon",
											"children" => array()
										 );										 
									
										 
				/**
				 * -----------------------------------				 
				 * GETTING TABLES FOR ACTUAL DATABASE.
				 * -----------------------------------
				 */
				//query the mysql
				$sql = "show tables from ".$databases[ $key ]["label"];
								
				$result = mysql_query( $sql, $this->connection );
				
				//in this var we will store the data of the tables
				$tables = array();
				
				//---
				// Fetching data
				//---
				while( $row = mysql_fetch_array( $result ) )
				{
					/** 
					 * --------------------------
					 * GET INFORMATION FOR TABLE 
					 * --------------------------
					 **/
					 
					//---
					// Getting columns
					//--- 
					$c_sql = "show columns from ".$row[ 0 ]." from ".$databases[ $key ]["label"];
					
					$c_result = mysql_query( $c_sql, $this->connection );
					
					$columns = array();
					
					while ( $c_row = mysql_fetch_array( $c_result ) ) 
					{
						$columns []= array( "label" => $c_row[ 0 ],
											"data" => strtolower( $c_row[ 0 ] ),
											"icon" => "mxColumnIcon"
										  );
					}
					//---
					// End getting columns
					//---
					
					
					//---
					// Getting indexes
					//--- 
					$i_sql = "show indexes from ".$row[ 0 ]." from ".$databases[ $key ]["label"];
					
					$i_result = mysql_query( $i_sql, $this->connection );
					
					$indexes = array();
					
					while ( $i_row = mysql_fetch_assoc( $i_result ) ) 
					{
						$indexes []= array( "label" => $i_row[ 'Column_name' ].", ".$i_row[ 'Key_name' ],
											"data" => strtolower( $i_row[ 0 ] ),
											"icon" => "mxIndexIcon"
										  );
					}
					//---
					// End getting indexes
					//---
					
					/** 
					 * Contents of one table tree element 
					 */
					$tableContents = array( 
										array(
											"label" => "Columns", 
											"data" => "columns",
											"icon" => "mxFolderIcon",
											"children" => $columns
										),
										array(
											"label" => "Indexes", 
											"data" => "indexes",
											"icon" => "mxFolderIcon",
											"children" => $indexes
										)
									);
					
					// Adding current table to tables..
					$tables []= array( "label" => $row[ 0 ],
									   "data" => strtolower( $row[ 0 ] ),
									   "icon" => "mxTableIcon",
									   "children" => $tableContents
									 );
				}
				//---
				// End fetching data
				//---
				
				$tables = array( "label" => "Tables",
								 "data" => "tables",
								 "icon" => "mxFolderIcon",
								 "children" => $tables
							 );
				
				//adding tables child to db instance
				$databases[ $key ]["children"] []= $tables;
				
				
				/**
				 * ------------------------------------
				 * GETTING VIEWS FOR ACTUAL DATABASE.
				 * ------------------------------------
				 */
				$views = array();
				
				$views = array( "label" => "Views",
								 "data" => "views",
								 "icon" => "mxFolderIcon",
								 "children" => array()
							 );
				
				//Query the db for the list of views
				$vw_sql = "select table_name from information_schema.views where table_schema = '".$databases[ $key ]["label"]."'";
				
				$vw_result = mysql_query( $vw_sql, $this->connection );
				
				//---
				// Fetching data: Creating the list of views
				//---
				while ( $row = mysql_fetch_assoc( $vw_result ) ) 
				{
					$views["children"] []= array(
											 "label" => $row['table_name'],
											 "data" => $row['table_name'],
											 "icon" => "mxViewIcon"								 
										 );
				}				
				//---
				// End fetching data
				//---
				
				//adding views child to db instance
				$databases[ $key ]["children"] []= $views;
				
			}			
			
			return array( "status" => "OK", "details" => "IS_VECTOR_RESULT", "result" => $databases );
		}
		//---
		// End of function
		//---
		
	 
	    function dbLogin( $username, $password )
	    {
	    	if( $username == DB_USER_NAME )
			{
				if( $password == DB_PASSWORD )
				{
					return array( "status" => "OK", "details" => "ACCESS_GRANTED" );
				}
				else
				{
					return array( "status" => "ERROR", "errorType" => "ACCESS", "details" => "INVALID_PASSWORD" );
				}
			}
			else
			{
				return array( "status" => "ERROR", "errorType" => "ACCESS", "details" => "INVALID_USERNAME" );
			}
	    }
	}
?>
